package com.fg.ihr.service.serviceImpl;

import com.fg.ihr.common.MyException;
import com.fg.ihr.domain.*;
import com.fg.ihr.repository.*;
import com.fg.ihr.service.UserService;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.unit.DataUnit;
import org.springframework.web.multipart.MultipartFile;
import com.fg.ihr.domain.User;


import javax.persistence.criteria.*;

import java.io.*;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Service
@Transactional
public class UserServiceImpl implements UserService {

    @Autowired
    private UserRepository userRepository;
    @Autowired
    private EducationRepository educationRepository;
    @Autowired
    private LevelRepository levelRepository;
    @Autowired
    private PositionRepository positionRepository;
    @Autowired
    private StatusRepository statusRepository;
    @Autowired
    private WardRepository wardRepository;
    @Autowired
    private LocationRepository locationRepository;
    @Autowired
    private TitleRepository titleRepository;
    @Autowired
    private DepartmentRepository departmentRepository;

    @Value("${file.downLoadFolder}")
    private String downloadFolder;

    @Override
    public Page<User> findByPageAndParams(String search, List<Ward> wardList, List<Integer> educationList,List<Integer> titleList,List<Integer> positionList,List<Integer> statusList, List<Integer> levelList, int page, int size, String orderBy, String sortOrder) {
        String sortorder = sortOrder.toUpperCase();
        Sort sort = new Sort(Sort.Direction.valueOf(sortorder), orderBy);
        Pageable pageable = PageRequest.of(page - 1, size, sort);
        if (search != null && !search.equals("") || wardList.size() !=0 ||  educationList.size() !=0 || titleList.size() !=0 ||positionList.size() !=0 ||  statusList.size() !=0 || levelList.size() !=0) {
            Page<User> result = userRepository.findAll(new Specification<User>() {
                @Override
                public Predicate toPredicate(Root<User> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                    List<Predicate> list = new ArrayList<Predicate>();
                    List<Predicate> wlist = new ArrayList<Predicate>();
                    List<Predicate> elist = new ArrayList<Predicate>();
                    List<Predicate> tlist = new ArrayList<Predicate>();
                    List<Predicate> slist = new ArrayList<Predicate>();
                    List<Predicate> llist = new ArrayList<Predicate>();
                    List<Predicate> plist = new ArrayList<Predicate>();
                    List<Predicate> sumlist = new ArrayList<Predicate>();
                    if (search != null && !search.equals("")) {
                        list.add(cb.like(root.get("name").as(String.class), "%" + search + "%"));
                        list.add(cb.like(root.get("idnumber").as(String.class), "%" + search + "%"));
                        Predicate[] p = new Predicate[list.size()];
                        Predicate SearchOR = cb.or(list.toArray(p));
                        sumlist.add(SearchOR);
                    }
                //查询职务
                    if (positionList.size() != 0 && !positionList.isEmpty()) {
                        ListJoin<User,Position> join = root.join(root.getModel().getList("positionList",Position.class),JoinType.LEFT);
                        CriteriaBuilder.In<Object> in = cb.in(join.get("id"));
                        for (Integer i : positionList) {
                            in.value(i);
                            plist.add(in);
                        }
                        Predicate[] po = new Predicate[plist.size()];
                        Predicate PostionOR = cb.or(plist.toArray(po));
                        sumlist.add(PostionOR);
                    }
                    if (wardList.size() != 0 && !wardList.isEmpty()) {
                        Join<Ward, User> join = root.join("ward", JoinType.LEFT);
                        CriteriaBuilder.In<Object> in = cb.in(join.get("id"));
                        for (Ward ward : wardList) {
                            in.value(ward.getId());
                            wlist.add(in);
                        }
                        Predicate[] w = new Predicate[wlist.size()];
                        Predicate WardOR = cb.or(wlist.toArray(w));
                        sumlist.add(WardOR);
                    }
                    //查询教育
                    if (educationList.size() != 0 && !educationList.isEmpty()) {
                        for (Integer education : educationList) {
                            Join<Education, User> join = root.join("education", JoinType.LEFT);
                            elist.add(cb.equal(join.get("id"), education));
                        }
                        Predicate[] e = new Predicate[elist.size()];
                        Predicate EducationOR = cb.or(elist.toArray(e));
                        sumlist.add(EducationOR);
                    }
                    //查询能级
                    if (levelList.size() != 0 && !levelList.isEmpty()) {
                        for (Integer level : levelList) {
                            Join<Level, User> join = root.join("level", JoinType.LEFT);
                            llist.add(cb.equal(join.get("id"), level));
                        }
                        Predicate[] l = new Predicate[llist.size()];
                        Predicate LevelOR = cb.or(llist.toArray(l));
                        sumlist.add(LevelOR);
                    }

                    //查询职称
                    if (titleList.size() != 0 && !titleList.isEmpty()) {
                        for (Integer title : titleList) {
                            Join<Title, User> join = root.join("title", JoinType.LEFT);
                            tlist.add(cb.equal(join.get("id"), title));
                        }
                        Predicate[] t = new Predicate[tlist.size()];
                        Predicate TitleOR = cb.or(tlist.toArray(t));
                        sumlist.add(TitleOR);
                    }

                    //查询状态
                    if (statusList.size() != 0 && !statusList.isEmpty()) {
                        for (Integer status : statusList) {
                            Join<Status, User> join = root.join("status", JoinType.LEFT);
                            slist.add(cb.equal(join.get("id"), status));
                        }
                        Predicate[] s = new Predicate[slist.size()];
                        Predicate StatusOR = cb.or(slist.toArray(s));
                        sumlist.add(StatusOR);
                    }

                    Predicate[] sum = new Predicate[sumlist.size()];
                    Predicate SumAnd = cb.and(sumlist.toArray(sum));
                    return SumAnd;
                }
            },pageable);
            return result;
        } else {
            Page<User> result = userRepository.findAll(pageable);
            return result;
        }
    };


    @Transactional(readOnly = false,rollbackFor = Exception.class)
    @Override
    public boolean batchImport(String fileName, MultipartFile file) throws Exception {
        boolean notNull = false;
        List<User> userList = new ArrayList<>();
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            throw new MyException("上传文件格式不正确");
        }
        boolean isExcel2003 = true;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }
        InputStream is = file.getInputStream();
        Workbook wb = null;
        if (isExcel2003) {
            wb = new HSSFWorkbook(is);
        } else {
            wb = new XSSFWorkbook(is);
        }
        Sheet sheet = wb.getSheetAt(0);
        if (sheet != null) {
            notNull = true;
        }
        User user;
        for (int r = 2; r <= sheet.getLastRowNum(); r++) {//r = 2 表示从第三行开始循环 如果你的第三行开始是数据
            Row row = sheet.getRow(r);//通过sheet表单对象得到 行对象
            if (row == null) {
                continue;
            }
            user = new User();
            if (row.getCell(0).getCellTypeEnum() != CellType.STRING) {//循环时，得到每一行的单元格进行判断
                throw new MyException("导入失败(第" + (r + 1) + "行,病房请设为文本格式)");
            }
            String wardname = row.getCell(0).getStringCellValue();//得到每一行第一个单元格的值
            for (Ward userward : wardRepository.findAll()) {
                if (userward.getWardname().equals(wardname)) {
                    user.setWard(wardRepository.getOne(userward.getId()));
                    break;
                }
            }
            ;

            if (row.getCell(1).getCellTypeEnum() != CellType.STRING) {//循环时，得到每一行的单元格进行判断
                throw new MyException("导入失败(第" + (r + 1) + "行,病房请设为文本格式)");
            }

            String name = row.getCell(1).getStringCellValue();
            if (name == null || name.isEmpty()) {
                throw new MyException("导入失败(第" + (r + 1) + "行,名字未填写)");
            } else {
                user.setName(name);
            }

            if (row.getCell(2).getCellTypeEnum() != CellType.STRING) {//循环时，得到每一行的单元格进行判断
                throw new MyException("导入失败(第" + (r + 1) + "行,工号请设为文本格式)");
            }

            String worknumber = row.getCell(2).getStringCellValue();
            if (worknumber == null || worknumber.isEmpty()) {
                throw new MyException("导入失败(第" + (r + 1) + "行,工号未填写)");
            } else {
                user.setWorknumber(worknumber);
            }

            if (row.getCell(3).getCellTypeEnum() != CellType.STRING) {//循环时，得到每一行的单元格进行判断
                throw new MyException("导入失败(第" + (r + 1) + "行,工号请设为文本格式)");
            }

            String sex = row.getCell(3).getStringCellValue();
            if (sex == null || sex.isEmpty()) {
                throw new MyException("导入失败(第" + (r + 1) + "行,工号未填写)");
            } else {
                if (sex.equals("男")) {
                    user.setSex(true);
                } else if (sex.equals("女")) {
                    user.setSex(false);
                }
            }

            if (!HSSFDateUtil.isCellDateFormatted(row.getCell(4))) {
                throw new MyException("导入失败(第" + (r + 1) + "行,日期请设为yyyy/MM/dd格式)");
            }

            Date bdate = row.getCell(4).getDateCellValue();
            if (bdate == null) {
                throw new MyException("导入失败(第" + (r + 1) + "行,出生日期未填写)");
            } else {
                user.setBirthday(bdate);
            }

            if (!HSSFDateUtil.isCellDateFormatted(row.getCell(5))) {
                throw new MyException("导入失败(第" + (r + 1) + "行,入职日期请设为yyyy/mm/dd格式)");
            }
            Date rdate = row.getCell(5).getDateCellValue();
            if (rdate == null) {
                throw new MyException("导入失败(第" + (r + 1) + "行,入职日期未填写)");
            } else {
                user.setRegdate(rdate);
            }

            if (row.getCell(6).getCellTypeEnum() != CellType.STRING) {//循环时，得到每一行的单元格进行判断
                throw new MyException("导入失败(第" + (r + 1) + "行,学历请设为文本格式)");
            }
            String edu = row.getCell(6).getStringCellValue();//得到每一行第一个单元格的值
            for (Education education : educationRepository.findAll()) {
                if (education.getName().equals(edu)) {
                    user.setEducation(educationRepository.getOne(education.getId()));
                    break;
                }
            }
            ;
            if (row.getCell(7).getCellTypeEnum() != CellType.STRING) {//循环时，得到每一行的单元格进行判断
                throw new MyException("导入失败(第" + (r + 1) + "行,职称请设为文本格式)");
            }
            String tit = row.getCell(7).getStringCellValue();//得到每一行第一个单元格的值
            if (tit == null || tit.isEmpty()) {
                throw new MyException("导入失败(第" + (r + 1) + "行,职称未填写)");
            }else {
                for (Title title : titleRepository.findAll()) {
                    if (title.getName().equals(tit)) {
                        user.setTitle(titleRepository.getOne(title.getId()));
                        break;
                    }
                }
            };

            if (row.getCell(8).getCellTypeEnum() != CellType.STRING) {//循环时，得到每一行的单元格进行判断
                throw new MyException("导入失败(第" + (r + 1) + "行,能级请设为文本格式)");
            }
            String lev = row.getCell(8).getStringCellValue();//得到每一行第一个单元格的值
            if (lev == null || lev.isEmpty()) {
                throw new MyException("导入失败(第" + (r + 1) + "行,能级未填写)");
            }else {
                for (Level level : levelRepository.findAll()) {
                    if (level.getName().equals(lev)) {
                        user.setLevel(levelRepository.getOne(level.getId()));
                        break;
                    }
                }
            };

            if (row.getCell(9).getCellTypeEnum() != CellType.STRING) {//循环时，得到每一行的单元格进行判断
                throw new MyException("导入失败(第" + (r + 1) + "行,职务请设为文本格式)");
            }
            String pos = row.getCell(9).getStringCellValue();//得到每一行第一个单元格的值
            if (pos == null || pos.isEmpty()) {
                throw new MyException("导入失败(第" + (r + 1) + "行,该职务未填写)");
            }
                String[] Str2Array = pos.split(",");
                List<Position> positionList = new ArrayList<>();
                for (String item : Str2Array) {
                    Position pname = positionRepository.findAllByName(item);
                    if (item.equals(pname.getName())) {
//                    System.out.print("职务是:" + pname.getName() + "编号是" + pname.getId());
                        positionList.add(pname);
                    }
                user.setPositionList(positionList);
//            if (positionList.size() == 0) {
//                throw new MyException("导入失败(第" + (r + 1) + "行,职务未填写)");
//            } else {
            }

            if (row.getCell(10).getCellTypeEnum() != CellType.NUMERIC) {//循环时，得到每一行的单元格进行判断
                throw new MyException("导入失败(第" + (r + 1) + "行,手机号码设为文本格式)");
            }

            DecimalFormat df = new DecimalFormat("0");
            String phonenumber = df.format(row.getCell(10).getNumericCellValue());
            //得到每一行第一个单元格的值
                if (phonenumber == null) {
                    throw new MyException("导入失败(第" + (r + 1) + "行,手机未填写)");
                } else {
                    user.setPhonenumber(phonenumber);
                }

            if (row.getCell(11).getCellTypeEnum() != CellType.STRING) {//循环时，得到每一行的单元格进行判断
                throw new MyException("导入失败(第" + (r + 1) + "行,身份证请设为文本格式)");
            }
            String idnumber = row.getCell(11).getStringCellValue();//得到每一行第一个单元格的值
            if (idnumber == null || idnumber.isEmpty()) {
                throw new MyException("导入失败(第" + (r + 1) + "行,身份证未填写)");
            } else {
                user.setIdnumber(idnumber);
            }


            if (row.getCell(12).getCellTypeEnum() != CellType.STRING) {//循环时，得到每一行的单元格进行判断
                throw new MyException("导入失败(第" + (r + 1) + "行,请设护士注册证号码为文本格式)");
            }
            String regnumber = row.getCell(12).getStringCellValue();//得到每一行第一个单元格的值
            if (regnumber == null || regnumber.isEmpty()) {
                throw new MyException("导入失败(第" + (r + 1) + "行,护士注册证号码未填写)");
            } else {
                user.setRegnumber(regnumber);
            }

            if (row.getCell(13).getCellTypeEnum() != CellType.STRING) {//循环时，得到每一行的单元格进行判断
                throw new MyException("导入失败(第" + (r + 1) + "行,请设银行卡账号为文本格式)");
            }
            String banknumber = row.getCell(13).getStringCellValue();//得到每一行第一个单元格的值
            if (banknumber == null || banknumber.isEmpty()) {
                throw new MyException("导入失败(第" + (r + 1) + "行,银行卡账号未填写)");
            } else {
                user.setBankaccount(banknumber);
            }

            if (row.getCell(14).getCellTypeEnum() != CellType.STRING) {//循环时，得到每一行的单元格进行判断
                throw new MyException("导入失败(第" + (r + 1) + "行,能级请设为文本格式)");
            }
            String status = row.getCell(14).getStringCellValue();//得到每一行第一个单元格的值
            for (Status statu : statusRepository.findAll()) {
                if (statu.getName().equals(status)) {
                    user.setStatus(statusRepository.getOne(statu.getId()));
                    break;
                }
            }

            //完整的循环一次 就组成了一个对象
            userList.add(user);
        }

        int addNum = 0;
        int updateNum = 0;
        for (User userResord : userList) {
            String name = userResord.getName();
            String password = userResord.getPassword();
            int cnt = userRepository.selectByName(name);
            if (cnt == 0) {
                addNum++;
                userRepository.saveAndFlush(userResord);
                System.out.println(" 插入 "+userResord.getPositionList());
            } else {
                updateNum++;
                userRepository.updateUserByName(name, password);
                System.out.println(" 更新 " + userResord.getPositionList());
            }
        }
        System.out.print("更新了:" + updateNum + "  " + "新增了" + addNum);
        return notNull;
    }

    //导出模板
    @Override
    public void TempExport(String ExportName) throws Exception {
        FileInputStream fis = new FileInputStream(downloadFolder + "template.xlsx");
        XSSFWorkbook workBook = new XSSFWorkbook(fis);
        Sheet sheet = workBook.getSheet("数据源");
        List<Ward> wards = wardRepository.findAll();
        List<Position> pos = positionRepository.findAll();
        List<Level> levels = levelRepository.findAll();
        List<Education> edus = educationRepository.findAll();
        List<Title> titles = titleRepository.findAll();
        List<Status> stats = statusRepository.findAll();

        for (int i = 0; i < wards.size(); i++) {
            Row creRow = sheet.createRow(i);
            creRow.createCell(0).setCellValue(wards.get(i).getWardname().toString());
        }
        for (int p = 0; p < pos.size(); p++) {
            Row creRow = sheet.getRow(p);
            creRow.createCell(1).setCellValue(pos.get(p).getName().toString());
        }
        for (int p = 0; p < levels.size(); p++) {
            Row creRow = sheet.getRow(p);
            creRow.createCell(2).setCellValue(levels.get(p).getName().toString());
        }
        for (int p = 0; p < levels.size(); p++) {
            Row creRow = sheet.getRow(p);
            creRow.createCell(2).setCellValue(levels.get(p).getName().toString());
        }
        for (int p = 0; p < edus.size(); p++) {
            Row creRow = sheet.getRow(p);
            creRow.createCell(3).setCellValue(edus.get(p).getName().toString());
        }
        for (int p = 0; p < titles.size(); p++) {
            Row creRow = sheet.getRow(p);
            creRow.createCell(4).setCellValue(titles.get(p).getName().toString());
        }
        for (int p = 0; p < stats.size(); p++) {
            Row creRow = sheet.getRow(p);
            creRow.createCell(5).setCellValue(stats.get(p).getName().toString());
        }
//            String fileName = "template" + currenttime + ".xlsx";
            OutputStream out = new FileOutputStream(ExportName);
//        workBook.removeSheetAt(0); // 移除workbook中的模板sheet
            workBook.write(out);
            fis.close();
            out.flush();
            out.close();
        }
        // 动态插入数据-增加行
}
//                    if (sumlist.size() == 1 && search.equals("")) {
//                        return SumAnd;
//                    }else if (sumlist.size() == 1 && !search.equals("")){
//                        return SearchOR;
//                    }
//                    else {
//                        query.where(SearchOR, SumAnd);
//                        return query.getRestriction();
//                    }
//                }